using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace umlungu.engineX {
	/// <summary>
	/// umlungu EngineX x_DbData class. base utility class for accessing Sql database.
	/// </summary>
	/// <remarks>
	/// This class is similar to the x_SqlData class but implements a model to ensure that database resources (ie connections) are closed.
	/// Also adds a few additional methods to the x_SqlData class.
	/// </remarks>
	public class x_DbData : IDisposable {
		#region Invisible properties
		private bool disposed = false;
		private SqlConnection xdb;
		private SqlCommand xcmd;
		#endregion

		#region Visible properties
		private string dsn;
		/// <summary>Database connection string</summary>
		/// <value>Connection parameters (normally set in the web.config file)</value>
		public string DSN {
			get {
				return dsn;
			}
			set {
				dsn = value;
			}
		}
		#endregion

		#region Constructors/Destructors
		/// <overloads>Constructor</overloads>
		/// <summary>Default constructor</summary>
		public x_DbData() {
		}
		/// <summary>
		/// Ordinary constructor for the x_DbData class.
		/// </summary>
		public x_DbData(string thisdsn) {
			dsn = thisdsn;
			xdb = new SqlConnection(dsn);
			xdb.Open();
		}
		/// <summary>This destructor will run only if the Dispose method does not get called.</summary>
		/// <remarks>
		/// Do not provide destructors in types derived from this class.
		/// </remarks>
		~x_DbData() {
			Dispose(false);
		}
		#endregion

		#region Public methods
		/// <summary>
		/// Opens the connection to the database.
		/// </summary>
		public void Open(string thisdsn) {
			dsn = thisdsn;
			xdb = new SqlConnection(dsn);
			xdb.Open();
		}
		/// <summary>
		/// Closes the x_DbData object, by closing the connection to the database.
		/// </summary>
		public void Close() {
		   Dispose();
		}
		/// <summary>
		/// Disposes the object by implementing IDisposable.
		/// </summary>
		/// <remarks>
		/// A derived class should not be able to override this method.
		/// </remarks>
		public void Dispose() {
			Dispose(true);
			// Take it off the Finalization queue to prevent finalization code for this object from executing a second time.
			GC.SuppressFinalize(this);
		}
		/// <summary>
		/// Does the disposing either from the public call, or from the destructor
		/// </summary>
		/// <remarks>
		/// A derived class may override this method in order to dispose of resources that it may create.
		/// It should call this by: base.Dispose(disposing);
		/// Dispose(bool disposing) executes in two distinct scenarios:
		/// - If disposing equals true, the method has been called directly or indirectly by a user's code. Managed and unmanaged resources can be disposed.
		/// - If disposing equals false, the method has been called by the runtime from inside the finalizer and you should not reference other objects. Only unmanaged resources can be disposed.
		/// Note that this is not thread safe.
		/// - Another thread could start disposing the object after the managed resources are disposed, but before the disposed flag is set to true.
		/// - If thread safety is necessary, it must be implemented by the client.
		/// </remarks>
		/// <param name="disposing">flag to indicate dispose in progress</param>
		protected virtual void Dispose(bool disposing) {
			// Check to see if Dispose has already been called.
			if(!this.disposed) {
				// If disposing equals true, dispose all managed and unmanaged resources.
				if(disposing) {
					// Dispose the database connection.
					xdb.Dispose();
				}
				// Release unmanaged resources. If disposing is false, only the following code is executed.
				// eg: CloseHandle(handle);
				// eg: handle = IntPtr.Zero;
			}
			disposed = true;         
		}
		#endregion
		
		#region Protected Db utility methods
		/// <summary>
		/// Adds an output parameter to a stored procedure call (integer)
		/// </summary>
		protected void _getCommand(string cmdText, CommandType cmdType) {
			xcmd = new SqlCommand(cmdText, xdb);
			xcmd.CommandType = cmdType;
		}
		/// <summary>
		/// Adds an output parameter to a stored procedure call (integer)
		/// </summary>
		protected IDataParameter _addDbParameter(string parname) {
			SqlParameter par = new SqlParameter(parname, SqlDbType.Int);
			par.Direction = ParameterDirection.Output;
			xcmd.Parameters.Add(par);
			return (IDataParameter) par;
		}
		/// <summary>
		/// Adds an output parameter to a stored procedure call (string)
		/// </summary>
		protected IDataParameter _addDbParameter(string parname, int parlen) {
			SqlParameter par = new SqlParameter(parname, SqlDbType.VarChar, parlen);
			par.Direction = ParameterDirection.Output;
			xcmd.Parameters.Add(par);
			return(par);
		}
		/// <summary>
		/// Adds an parameter to a stored procedure call (integer)
		/// </summary>
		protected void _addDbParameter(int parval, string parname) {
			SqlParameter par = new SqlParameter(parname, SqlDbType.Int);
			par.Value = parval;
			xcmd.Parameters.Add(par);
		}
		/// <summary>
		/// Adds an parameter to a stored procedure call (string)
		/// </summary>
		protected void _addDbParameter(string parval, string parname, int parlen) {
			SqlParameter par = new SqlParameter(parname, SqlDbType.VarChar, parlen);
			par.Value = parval;
			xcmd.Parameters.Add(par);
		}
		/// <summary>
		/// Adds an parameter to a stored procedure call (uid)
		/// </summary>
		protected void _addDbUid(string parval, string parname) {
			SqlParameter par = new SqlParameter(parname, SqlDbType.VarChar, 65);
			par.Value = parval;
			xcmd.Parameters.Add(par);
		}
		/// <summary>
		/// Execute a query or stored procedure (no reader)
		/// </summary>
		protected int  _executeNonQuery() {
			return xcmd.ExecuteNonQuery();
		}
		/// <overloads>Execute a query or stored procedure (return reader)</overloads>
		/// <summary>
		/// Uses default CommandBehavior
		/// </summary>
		protected IDataReader _executeReader() {
			return _executeReader(CommandBehavior.Default);
		}
		/// <summary>
		/// Uses supplied CommandBehavior
		/// </summary>
		/// <param name="behavior">CommandBehavior used to </param>
		protected IDataReader _executeReader(CommandBehavior behavior) {
			return xcmd.ExecuteReader(behavior);
		}
		/// <summary>
		/// Adds an output parameter to a stored procedure call (integer)
		/// </summary>
		protected object _getParameter(IDataParameter par) {
			return par.Value;
		}
		#endregion
				
		#region Protected XML utility methods
		/// <summary>
		/// Adds an attribute to a node of a document from a field in a recordset
		/// </summary>
		protected bool _addAttribute(XmlDocument xmlThis, XmlNode xnThis, SqlDataReader rdrThis, string attname, string fldname) {
			try {
				XmlAttribute xaThis = xmlThis.CreateAttribute(attname);
				if(!(rdrThis.IsDBNull(rdrThis.GetOrdinal(fldname))))
					xaThis.InnerText = rdrThis.GetSqlValue(rdrThis.GetOrdinal(fldname)).ToString();
				xnThis.Attributes.Append(xaThis);
				return(true);
			} catch {
				return(false);
			}
		}
		/// <summary>
		/// Adds an attribute to a node of a document
		/// </summary>
		protected bool _addAttribute(XmlDocument xmlThis, XmlNode xnThis, string attname, string attval) {
			try {
				XmlAttribute xaThis = xmlThis.CreateAttribute(attname);
				xaThis.InnerText = attval;
				xnThis.Attributes.Append(xaThis);
				return(true);
			} catch {
				return(false);
			}
		}
		/// <summary>
		/// Adds an element to a node of a document
		/// </summary>
		protected bool _addElement(XmlDocument xmlThis, XmlNode xnThis, string elname, string elval) {
			try {
				XmlElement xeThis = xmlThis.CreateElement(elname);
				xeThis.InnerText = elval;
				xnThis.AppendChild(xeThis);
				return(true);
			} catch {
				return(false);
			}
		}
		/// <summary>
		/// Adds an element and its attribute to a node of a document
		/// </summary>
		protected bool _addElement(XmlDocument xmlThis, XmlNode xnThis, string elname, string elval, string attname, string attval) {
			if(_addElement(xmlThis, xnThis, elname, elval))
				return(_addAttribute(xmlThis, xnThis.SelectSingleNode(elname), attname, attval));
			else
				return(false);
		}
		/// <summary>
		/// Adds an element and its attribute to a node of a document
		/// </summary>
		protected bool _addElement(XmlDocument xmlThis, XmlNode xnThis, string nodename, string elname, string elval, string attname, string attval) {
			if(_addElement(xmlThis, xnThis, nodename, "")) {
				XmlNode nde = xnThis.SelectSingleNode(nodename);
				if(_addElement(xmlThis, nde, elname, elval))
					return(_addAttribute(xmlThis, nde, attname, attval));
				else
					return(false);
			}
			else
				return(false);
		}
		/// <summary>
		/// A safe method of getting a string from a SqlDataReader column when the value could be null
		/// </summary>
		protected string _getString(SqlDataReader thisrdr, string thiscol) {
			int thisord = thisrdr.GetOrdinal(thiscol);
			return (thisrdr.IsDBNull(thisord))? "" : thisrdr.GetValue(thisord).ToString();
		}
		/// <summary>
		/// A safe method of getting a datetime object from a SqlDataReader column when the value could be null
		/// </summary>
		protected string _getDate(SqlDataReader thisrdr, string thiscol) {
			int thisord = thisrdr.GetOrdinal(thiscol);
			if (thisrdr.IsDBNull(thisord))
				return "";
			else {
				DateTime thisdt = thisrdr.GetDateTime(thisord); 
				return thisdt.ToString("s", DateTimeFormatInfo.InvariantInfo);
			}
		}
		/// <summary>
		/// A safe method of getting a boolean result from a SqlDataReader column when the value could be null
		/// </summary>
		protected string _getYesNo(SqlDataReader thisrdr, string thiscol) {
			int thisord = thisrdr.GetOrdinal(thiscol);
			return (thisrdr.IsDBNull(thisord))? "no" : "yes";
		}
		#endregion
				
		#region Private methods
		#endregion
	}
}
